﻿using Oracle.ManagedDataAccess.Client;
using SqlKata;
using SqlKata.Compilers;
using SqlKata.Execution;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SqlBuilder
{
    public static class Functions
    {
        static string connectionStr =Configuration.Settings.ConnectionString;
        static string userName = Configuration.Settings.DBUserName;

        public static async Task<List<TableStructure>> GetTableStructure()
        {
            List<TableStructure> res= new List<TableStructure>();

            OracleConnection connection = new OracleConnection(connectionStr);
            var compiler = new OracleCompiler { };
            var db = new QueryFactory(connection, compiler);
            var tablesQ = db.Query("USER_TABLES").LeftJoin("USER_TAB_COMMENTS", "USER_TABLES.TABLE_NAME", "USER_TAB_COMMENTS.TABLE_NAME").Select("USER_TABLES.TABLE_NAME", "COMMENTS").OrderBy("USER_TABLES.TABLE_NAME");

            //SqlResult result = compiler.Compile(tablesQ);
            //Console.WriteLine(result.Sql);
            var tables = await tablesQ.GetAsync();
            Dictionary<string, TableStructure> dic = new Dictionary<string, TableStructure>();
            foreach (var row in tables)
            {
                var table = new TableStructure { Name = row.TABLE_NAME, Comments = row.COMMENTS };
                Console.WriteLine($"get table:{table.Name}\t{table.Comments}");
                res.Add(table);
                dic[table.Name] = table;
            }
            //select table_name,column_name,comments from all_col_comments where owner = 'RYHIS'
            var commentsQ = db.Query("ALL_COL_COMMENTS").Select("TABLE_NAME","COLUMN_NAME","COMMENTS").Where("OWNER",userName);
            //result = compiler.Compile(commentsQ);
            //Console.WriteLine(result.Sql);
            var comments = await commentsQ.GetAsync();
            foreach (var row in comments)
            {
                var column = new Column { Name = row.COLUMN_NAME, Comment = row.COMMENTS };
                var tableName = row.TABLE_NAME;
                if (dic.ContainsKey(tableName))
                {
                    Console.WriteLine($"get column:{tableName}\t{column.Name}\t{column.Comment}");
                    dic[tableName].Columns.Add(column);
                }
            }
            return res;
        }

        public static Dictionary<TableStructure,int> Find(IList<TableStructure> tables,IList<Column> columns)
        {
            var res = new Dictionary<TableStructure, int>();
            foreach (var table in tables)
            {
                int grade = 0;
                foreach (var column in columns)
                {
                    if (table.Columns.FirstOrDefault(c => c.UniformComment != null && c.UniformComment ==column.UniformComment) != null)
                    {
                        grade++;
                    }
                }
                if (grade != 0)
                {
                    res[table] = grade ;
                }
            }
            res = res.OrderByDescending(kv => kv.Value).ToDictionary(k=>k.Key,v=>v.Value);
            return res;
        }

        public static string CreateSql(IList<ColumnContrast> contrasts)
        {
            StringBuilder resBuilder = new StringBuilder();
            resBuilder.AppendLine("SELECT");
            Dictionary<string,string> tableNamedic = new Dictionary<string,string>();
            string tableNames = string.Empty;
            foreach (ColumnContrast contrast in contrasts)
            {
                if (string.IsNullOrEmpty(contrast.TableName))
                    continue;
                if (!tableNamedic.ContainsKey(contrast.TableName))
                {
                    tableNamedic[contrast.TableName] = "t" + (tableNamedic.Count + 1);
                    tableNames += $" {contrast.TableName} {tableNamedic[contrast.TableName]},";
                }
            }
            if (!string.IsNullOrEmpty(tableNames))
                tableNames = tableNames.Substring(0, tableNames.Length - 1);
            ColumnContrast contrastTemp;
            for (int i =0;i< contrasts.Count;i++)
            {
                contrastTemp = contrasts[i];
                string columnSql = string.Empty;
                if ((!string.IsNullOrEmpty(contrastTemp.TableName)) && contrastTemp.SourceColumn != null)
                {
                    columnSql = $"\t{tableNamedic[contrastTemp.TableName]}.{contrastTemp.SourceColumn.Name} as {contrastTemp.TargetColumn},--{contrastTemp.TargetComment} ";                 
                }
                else
                {
                    columnSql = $"\t /*--*/null as {contrastTemp.TargetColumn},--{contrastTemp.TargetComment}";
                }
                if (i == contrasts.Count - 1)
                    columnSql = columnSql.Replace(',', ' ');
                resBuilder.AppendLine(columnSql);
            }
            resBuilder.AppendLine($"FROM {tableNames}");
            return resBuilder.ToString();
        }
        public static string Translate(string str)
        {
            if (str == null) return string.Empty;
            str = str.Trim().ToUpper();
            if (Configuration.CommentsDic.ContainsKey(str))
                return Configuration.CommentsDic[str];
            return str;
        }

        public static void SetSingleton(this object obj)
        {
            Type type = obj.GetType();
            SerializeObjToFile(obj, Configuration.Singletons[type.ToString()]);
        }
        public static void SerializeObjToFile(object obj, string fileName)
        {
            string path = Environment.CurrentDirectory + @"\jsons\";
            if (!fileName.Contains(".json"))
                fileName += ".json";
            if (!Directory.Exists(path))
                Directory.CreateDirectory(path);
            using (TextWriter tw = new StreamWriter(Path.Combine(path, fileName)))
            {
                tw.WriteLine($"//{DateTime.Now:yyyy-MM-dd HH:mm:ss}");
                tw.WriteLine(SerializeObject(obj));
                tw.Close();
            }
        }

        public static T? GetSingleton<T>()
        {
            Type type = typeof(T);
            return DeserializeObjForFile<T>(Configuration.Singletons[type.ToString()]);
        }
        public static T? DeserializeObjForFile<T>(string fileName)
        {
            string path = Environment.CurrentDirectory + @"\jsons\";
            if (!fileName.Contains(".json"))
                fileName += ".json";
            string fullPath= Path.Combine(path, fileName);
            if (!File.Exists(fullPath))
                return default(T);
            using (StreamReader sr = new StreamReader(fullPath, Encoding.Default))
            {
                string jsonStr = sr.ReadToEnd();
                return DeserializeObject<T>(jsonStr);
            }            
        }
        public static string SerializeObject(object obj)
        {
            return Newtonsoft.Json.JsonConvert.SerializeObject(obj);
        }
        public static T? DeserializeObject<T>(string json)
        {
            return Newtonsoft.Json.JsonConvert.DeserializeObject<T>(json);
        }
    }
}
